package net.mingsoft.expertmanage.controller;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import net.mingsoft.base.entity.ResultData;
import net.mingsoft.expertmanage.entity.CompanyEntity;
import net.mingsoft.expertmanage.entity.ExpertEntity;
import net.mingsoft.expertmanage.entity.ProjectEntity;
import net.mingsoft.expertmanage.service.ICompanyService;
import net.mingsoft.expertmanage.service.IExpertService;
import net.mingsoft.expertmanage.service.IProjectService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * 专家关联查询控制器
 */
@Api(value = "专家关联查询接口")
@Controller
@RequestMapping("/${ms.manager.path}/expertmanage/relation")
public class RelationController {

    private static final Logger LOG = LoggerFactory.getLogger(RelationController.class);

    @Autowired
    private IExpertService expertService;
    
    @Autowired
    private IProjectService projectService;
    
    @Autowired
    private ICompanyService companyService;
    
    /**
     * 专家关联查询主页
     */
    @ApiOperation(value = "专家关联查询主页")
    @GetMapping("/index")
    public String index() {
        return "/expertmanage/relation/index";
    }
    
    /**
     * 健康检查接口 - 用于测试控制器是否正常响应
     */
    @ApiOperation(value = "健康检查")
    @GetMapping("/health")
    @ResponseBody
    public ResultData health() {
        return ResultData.build().success("RelationController运行正常");
    }
    
    /**
     * 获取专家关联信息
     * 根据专家编号查询相关项目和企业信息
     */
    @ApiOperation(value = "获取专家关联信息")
    @GetMapping("/getRelations")
    @ResponseBody
    public ResultData getRelations(@RequestParam("expertCode") String expertCode) {
        if (StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家编号不能为空");
        }
        
        // 1. 获取专家信息
        ExpertEntity expert = expertService.getByExpertCode(expertCode);
        if (expert == null) {
            return ResultData.build().error("未找到对应的专家信息");
        }
        
        // 2. 获取专家相关的项目
        List<ProjectEntity> projects = projectService.getByExpertCode(expertCode);
        
        // 3. 对每个项目，提取并关联企业信息
        List<Map<String, Object>> relationData = new ArrayList<>();
        Set<String> processedCompanyCodes = new HashSet<>(); // 跟踪已处理的企业编号以避免重复
        
        // 处理项目关联
        for (ProjectEntity project : projects) {
            // 从项目备注或合作倾向中提取企业编号
            Set<String> companyCodes = new HashSet<>();
            
            // 从项目合作企业倾向中提取企业编号
            if (StringUtils.isNotEmpty(project.getCooperationTendency())) {
                String companyCode = extractCompanyCode(project.getCooperationTendency());
                if (companyCode != null) {
                    companyCodes.add(companyCode);
                }
            }
            
            // 从项目备注中提取企业编号
            if (StringUtils.isNotEmpty(project.getRemarks())) {
                String companyCode = extractCompanyCode(project.getRemarks());
                if (companyCode != null) {
                    companyCodes.add(companyCode);
                }
            }
            
            // 如果找到企业编号，查询企业信息并添加到关联数据中
            if (!companyCodes.isEmpty()) {
                for (String companyCode : companyCodes) {
                    CompanyEntity company = companyService.getByCompanyCode(companyCode);
                    if (company != null) {
                        Map<String, Object> relationMap = new HashMap<>();
                        relationMap.put("project", project);
                        relationMap.put("company", company);
                        relationMap.put("relationType", "项目-企业关联");
                        relationData.add(relationMap);
                        processedCompanyCodes.add(companyCode);
                    }
                }
            } else {
                // 如果没有找到关联企业，仍然添加项目信息
                Map<String, Object> relationMap = new HashMap<>();
                relationMap.put("project", project);
                relationMap.put("relationType", "专家-项目关联");
                relationData.add(relationMap);
            }
        }
        
        // 4. 检查专家的备注中是否包含企业信息
        if (StringUtils.isNotEmpty(expert.getRemarks())) {
            String companyCode = extractCompanyCode(expert.getRemarks());
            if (companyCode != null && !processedCompanyCodes.contains(companyCode)) {
                CompanyEntity company = companyService.getByCompanyCode(companyCode);
                if (company != null) {
                    Map<String, Object> remarksRelationMap = new HashMap<>();
                    remarksRelationMap.put("company", company);
                    remarksRelationMap.put("relationType", "专家-企业备注关联");
                    relationData.add(remarksRelationMap);
                }
            }
        }
        
        // 5. 组装返回数据
        Map<String, Object> result = new HashMap<>();
        result.put("expert", expert);
        result.put("relations", relationData);
        
        return ResultData.build().success(result);
    }
    
    /**
     * 获取专家与项目的完整关联图谱数据
     */
    @ApiOperation(value = "获取专家关联图谱数据")
    @GetMapping("/getRelationChart")
    @ResponseBody
    public ResultData getRelationChart(@RequestParam("expertCode") String expertCode) {
        if (StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家编号不能为空");
        }
        
        // 1. 获取专家信息
        ExpertEntity expert = expertService.getByExpertCode(expertCode);
        if (expert == null) {
            return ResultData.build().error("未找到对应的专家信息");
        }
        
        // 2. 获取专家相关的项目
        List<ProjectEntity> projects = projectService.getByExpertCode(expertCode);
        
        // 3. 查找所有关联的企业编号
        Set<String> companyCodes = new HashSet<>();
        Map<String, ProjectEntity> projectMap = new HashMap<>();
        
        // 从项目中提取企业编号
        for (ProjectEntity project : projects) {
            projectMap.put(project.getProjectCode(), project);
            
            if (StringUtils.isNotEmpty(project.getCooperationTendency())) {
                String companyCode = extractCompanyCode(project.getCooperationTendency());
                if (companyCode != null) {
                    companyCodes.add(companyCode);
                }
            }
            
            if (StringUtils.isNotEmpty(project.getRemarks())) {
                String companyCode = extractCompanyCode(project.getRemarks());
                if (companyCode != null) {
                    companyCodes.add(companyCode);
                }
            }
        }
        
        // 4. 查询所有关联企业的信息
        List<CompanyEntity> companies = new ArrayList<>();
        for (String companyCode : companyCodes) {
            CompanyEntity company = companyService.getByCompanyCode(companyCode);
            if (company != null) {
                companies.add(company);
            }
        }
        
        // 5. 构建图谱节点和连接
        List<Map<String, Object>> nodes = new ArrayList<>();
        List<Map<String, Object>> links = new ArrayList<>();
        
        // 提取关键词和其他属性
        Set<String> researchKeywords = new HashSet<>();  // 研究方向关键词
        Set<String> disciplines = new HashSet<>();       // 学科领域
        Set<String> techKeywords = new HashSet<>();      // 技术关键词
        Set<String> industries = new HashSet<>();        // 应用行业
        Set<String> businessKeywords = new HashSet<>();  // 业务关键词
        
        // 添加专家节点
        Map<String, Object> expertNode = new HashMap<>();
        expertNode.put("id", "expert-" + expert.getExpertCode());
        expertNode.put("name", expert.getName());
        expertNode.put("category", 0); // 专家类别
        expertNode.put("value", expert.getExpertCode());
        expertNode.put("symbolSize", 60);
        nodes.add(expertNode);
        
        // 处理专家的学科领域
        if (StringUtils.isNotEmpty(expert.getFirstLevelDiscipline())) {
            disciplines.add(expert.getFirstLevelDiscipline());
            
            // 创建学科节点
            Map<String, Object> disciplineNode = new HashMap<>();
            String disciplineId = "discipline-" + disciplines.size();
            disciplineNode.put("id", disciplineId);
            disciplineNode.put("name", expert.getFirstLevelDiscipline());
            disciplineNode.put("category", 3); // 学科类别
            disciplineNode.put("value", expert.getFirstLevelDiscipline());
            disciplineNode.put("symbolSize", 35);
            nodes.add(disciplineNode);
            
            // 创建专家到学科的连接
            Map<String, Object> expertToDisciplineLink = new HashMap<>();
            expertToDisciplineLink.put("source", "expert-" + expert.getExpertCode());
            expertToDisciplineLink.put("target", disciplineId);
            expertToDisciplineLink.put("value", "所属学科");
            links.add(expertToDisciplineLink);
        }
        
        // 二级学科
        if (StringUtils.isNotEmpty(expert.getSecondLevelDiscipline())) {
            disciplines.add(expert.getSecondLevelDiscipline());
            
            // 创建二级学科节点
            Map<String, Object> disciplineNode = new HashMap<>();
            String disciplineId = "discipline-" + disciplines.size();
            disciplineNode.put("id", disciplineId);
            disciplineNode.put("name", expert.getSecondLevelDiscipline());
            disciplineNode.put("category", 3); // 学科类别
            disciplineNode.put("value", expert.getSecondLevelDiscipline());
            disciplineNode.put("symbolSize", 30);
            nodes.add(disciplineNode);
            
            // 创建专家到二级学科的连接
            Map<String, Object> expertToDisciplineLink = new HashMap<>();
            expertToDisciplineLink.put("source", "expert-" + expert.getExpertCode());
            expertToDisciplineLink.put("target", disciplineId);
            expertToDisciplineLink.put("value", "专业方向");
            links.add(expertToDisciplineLink);
        }
        
        // 提取专家研究方向关键词
        if (StringUtils.isNotEmpty(expert.getResearchKeywords())) {
            String[] keywords = expert.getResearchKeywords().split("、|,|，|;|；|\\s+");
            for (String keyword : keywords) {
                if (StringUtils.isNotEmpty(keyword) && keyword.length() > 1) {
                    researchKeywords.add(keyword.trim());
                    
                    // 创建研究方向节点
                    Map<String, Object> keywordNode = new HashMap<>();
                    String keywordId = "keyword-" + researchKeywords.size();
                    keywordNode.put("id", keywordId);
                    keywordNode.put("name", keyword.trim());
                    keywordNode.put("category", 4); // 关键词类别
                    keywordNode.put("value", keyword.trim());
                    keywordNode.put("symbolSize", 25);
                    nodes.add(keywordNode);
                    
                    // 创建专家到研究方向的连接
                    Map<String, Object> expertToKeywordLink = new HashMap<>();
                    expertToKeywordLink.put("source", "expert-" + expert.getExpertCode());
                    expertToKeywordLink.put("target", keywordId);
                    expertToKeywordLink.put("value", "研究方向");
                    links.add(expertToKeywordLink);
                }
            }
        }
        
        // 添加专家所属单位/学院节点
        if (StringUtils.isNotEmpty(expert.getCollege())) {
            Map<String, Object> collegeNode = new HashMap<>();
            String collegeId = "college-1";
            collegeNode.put("id", collegeId);
            collegeNode.put("name", expert.getCollege());
            collegeNode.put("category", 5); // 单位类别
            collegeNode.put("value", expert.getCollege());
            collegeNode.put("symbolSize", 40);
            nodes.add(collegeNode);
            
            // 创建专家到单位的连接
            Map<String, Object> expertToCollegeLink = new HashMap<>();
            expertToCollegeLink.put("source", "expert-" + expert.getExpertCode());
            expertToCollegeLink.put("target", collegeId);
            expertToCollegeLink.put("value", "所属单位");
            links.add(expertToCollegeLink);
        }
        
        // 添加项目节点和连接
        int techKeywordCount = 0;
        int industryCount = 0;
        
        for (ProjectEntity project : projects) {
            // 项目节点
            Map<String, Object> projectNode = new HashMap<>();
            projectNode.put("id", "project-" + project.getProjectCode());
            projectNode.put("name", project.getProjectName());
            projectNode.put("category", 1); // 项目类别
            projectNode.put("value", project.getProjectCode());
            projectNode.put("symbolSize", 40);
            nodes.add(projectNode);
            
            // 专家到项目的连接
            Map<String, Object> expertToProjectLink = new HashMap<>();
            expertToProjectLink.put("source", "expert-" + expert.getExpertCode());
            expertToProjectLink.put("target", "project-" + project.getProjectCode());
            
            // 根据专家在项目中的角色设置连接类型
            String relationRole = "参与者";
            if (project.getLeaderCode() != null && project.getLeaderCode().equals(expert.getExpertCode())) {
                relationRole = "负责人";
            } else if (project.getParticipantExperts() != null && project.getParticipantExperts().contains(expert.getExpertCode())) {
                relationRole = "参与者";
            }
            
            expertToProjectLink.put("value", relationRole);
            links.add(expertToProjectLink);
            
            // 添加项目部门节点
            if (StringUtils.isNotEmpty(project.getDepartment())) {
                String deptId = "dept-" + project.getDepartment().hashCode();
                
                // 检查是否已添加该部门节点
                boolean deptNodeExists = false;
                for (Map<String, Object> node : nodes) {
                    if (deptId.equals(node.get("id"))) {
                        deptNodeExists = true;
                        break;
                    }
                }
                
                if (!deptNodeExists) {
                    Map<String, Object> deptNode = new HashMap<>();
                    deptNode.put("id", deptId);
                    deptNode.put("name", project.getDepartment());
                    deptNode.put("category", 6); // 部门类别
                    deptNode.put("value", project.getDepartment());
                    deptNode.put("symbolSize", 35);
                    nodes.add(deptNode);
                }
                
                // 添加项目到部门的连接
                Map<String, Object> projectToDeptLink = new HashMap<>();
                projectToDeptLink.put("source", "project-" + project.getProjectCode());
                projectToDeptLink.put("target", deptId);
                projectToDeptLink.put("value", "所属部门");
                links.add(projectToDeptLink);
            }
            
            // 提取项目核心技术关键词
            if (StringUtils.isNotEmpty(project.getCoreTechKeywords())) {
                String[] keywords = project.getCoreTechKeywords().split("、|,|，|;|；|\\s+");
                for (String keyword : keywords) {
                    if (StringUtils.isNotEmpty(keyword) && keyword.length() > 1) {
                        techKeywords.add(keyword.trim());
                        techKeywordCount++;
                        
                        // 创建技术关键词节点
                        Map<String, Object> techNode = new HashMap<>();
                        String techId = "tech-" + techKeywordCount;
                        techNode.put("id", techId);
                        techNode.put("name", keyword.trim());
                        techNode.put("category", 7); // 技术关键词类别
                        techNode.put("value", keyword.trim());
                        techNode.put("symbolSize", 25);
                        nodes.add(techNode);
                        
                        // 创建项目到技术关键词的连接
                        Map<String, Object> projectToTechLink = new HashMap<>();
                        projectToTechLink.put("source", "project-" + project.getProjectCode());
                        projectToTechLink.put("target", techId);
                        projectToTechLink.put("value", "核心技术");
                        links.add(projectToTechLink);
                        
                        // 将技术关键词与研究方向关联（如果有匹配）
                        for (String researchKeyword : researchKeywords) {
                            if (keyword.contains(researchKeyword) || researchKeyword.contains(keyword)) {
                                for (Map<String, Object> node : nodes) {
                                    if (node.get("name").equals(researchKeyword) && node.get("category").equals(4)) {
                                        Map<String, Object> techToResearchLink = new HashMap<>();
                                        techToResearchLink.put("source", techId);
                                        techToResearchLink.put("target", node.get("id"));
                                        techToResearchLink.put("value", "相关研究");
                                        links.add(techToResearchLink);
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            
            // 提取应用行业
            if (StringUtils.isNotEmpty(project.getApplicationIndustry())) {
                String[] industryArray = project.getApplicationIndustry().split("、|,|，|;|；|\\s+");
                for (String industry : industryArray) {
                    if (StringUtils.isNotEmpty(industry) && industry.length() > 1) {
                        industries.add(industry.trim());
                        industryCount++;
                        
                        // 创建行业节点
                        Map<String, Object> industryNode = new HashMap<>();
                        String industryId = "industry-" + industryCount;
                        industryNode.put("id", industryId);
                        industryNode.put("name", industry.trim());
                        industryNode.put("category", 8); // 行业类别
                        industryNode.put("value", industry.trim());
                        industryNode.put("symbolSize", 30);
                        nodes.add(industryNode);
                        
                        // 创建项目到行业的连接
                        Map<String, Object> projectToIndustryLink = new HashMap<>();
                        projectToIndustryLink.put("source", "project-" + project.getProjectCode());
                        projectToIndustryLink.put("target", industryId);
                        projectToIndustryLink.put("value", "应用行业");
                        links.add(projectToIndustryLink);
                    }
                }
            }
        }
        
        // 添加企业节点和连接
        int businessKeywordCount = 0;
        for (CompanyEntity company : companies) {
            // 企业节点
            Map<String, Object> companyNode = new HashMap<>();
            companyNode.put("id", "company-" + company.getCompanyCode());
            companyNode.put("name", company.getCompanyName());
            companyNode.put("category", 2); // 企业类别
            companyNode.put("value", company.getCompanyCode());
            companyNode.put("symbolSize", 40);
            nodes.add(companyNode);
            
            // 企业类型节点
            if (StringUtils.isNotEmpty(company.getCompanyType())) {
                String companyTypeId = "companyType-" + company.getCompanyType().hashCode();
                
                // 检查是否已添加该企业类型节点
                boolean typeNodeExists = false;
                for (Map<String, Object> node : nodes) {
                    if (companyTypeId.equals(node.get("id"))) {
                        typeNodeExists = true;
                        break;
                    }
                }
                
                if (!typeNodeExists) {
                    Map<String, Object> typeNode = new HashMap<>();
                    typeNode.put("id", companyTypeId);
                    typeNode.put("name", company.getCompanyType());
                    typeNode.put("category", 9); // 企业类型类别
                    typeNode.put("value", company.getCompanyType());
                    typeNode.put("symbolSize", 30);
                    nodes.add(typeNode);
                }
                
                // 添加企业到企业类型的连接
                Map<String, Object> companyToTypeLink = new HashMap<>();
                companyToTypeLink.put("source", "company-" + company.getCompanyCode());
                companyToTypeLink.put("target", companyTypeId);
                companyToTypeLink.put("value", "企业类型");
                links.add(companyToTypeLink);
            }
            
            // 添加企业行业节点
            if (StringUtils.isNotEmpty(company.getIndustryPrimary())) {
                String industryId = "industry-pri-" + company.getIndustryPrimary().hashCode();
                
                // 检查是否已添加该行业节点
                boolean industryNodeExists = false;
                for (Map<String, Object> node : nodes) {
                    if (industryId.equals(node.get("id"))) {
                        industryNodeExists = true;
                        break;
                    }
                }
                
                if (!industryNodeExists) {
                    Map<String, Object> industryNode = new HashMap<>();
                    industryNode.put("id", industryId);
                    industryNode.put("name", company.getIndustryPrimary());
                    industryNode.put("category", 8); // 行业类别
                    industryNode.put("value", company.getIndustryPrimary());
                    industryNode.put("symbolSize", 30);
                    nodes.add(industryNode);
                }
                
                // 添加企业到行业的连接
                Map<String, Object> companyToIndustryLink = new HashMap<>();
                companyToIndustryLink.put("source", "company-" + company.getCompanyCode());
                companyToIndustryLink.put("target", industryId);
                companyToIndustryLink.put("value", "所属行业");
                links.add(companyToIndustryLink);
                
                // 将企业行业与项目应用行业关联
                for (String projectIndustry : industries) {
                    if (company.getIndustryPrimary().contains(projectIndustry) || 
                        projectIndustry.contains(company.getIndustryPrimary())) {
                        for (Map<String, Object> node : nodes) {
                            if (node.get("name").equals(projectIndustry) && node.get("category").equals(8)) {
                                Map<String, Object> industryLink = new HashMap<>();
                                industryLink.put("source", industryId);
                                industryLink.put("target", node.get("id"));
                                industryLink.put("value", "行业关联");
                                links.add(industryLink);
                                break;
                            }
                        }
                    }
                }
            }
            
            // 提取企业业务关键词
            if (StringUtils.isNotEmpty(company.getBusinessKeywords())) {
                String[] keywords = company.getBusinessKeywords().split("、|,|，|;|；|\\s+");
                for (String keyword : keywords) {
                    if (StringUtils.isNotEmpty(keyword) && keyword.length() > 1) {
                        businessKeywords.add(keyword.trim());
                        businessKeywordCount++;
                        
                        // 创建业务关键词节点
                        Map<String, Object> businessNode = new HashMap<>();
                        String businessId = "business-" + businessKeywordCount;
                        businessNode.put("id", businessId);
                        businessNode.put("name", keyword.trim());
                        businessNode.put("category", 10); // 业务关键词类别
                        businessNode.put("value", keyword.trim());
                        businessNode.put("symbolSize", 25);
                        nodes.add(businessNode);
                        
                        // 创建企业到业务关键词的连接
                        Map<String, Object> companyToBusinessLink = new HashMap<>();
                        companyToBusinessLink.put("source", "company-" + company.getCompanyCode());
                        companyToBusinessLink.put("target", businessId);
                        companyToBusinessLink.put("value", "业务方向");
                        links.add(companyToBusinessLink);
                        
                        // 将业务关键词与技术关键词关联
                        for (String techKeyword : techKeywords) {
                            if (keyword.contains(techKeyword) || techKeyword.contains(keyword)) {
                                for (Map<String, Object> node : nodes) {
                                    if (node.get("name").equals(techKeyword) && node.get("category").equals(7)) {
                                        Map<String, Object> businessToTechLink = new HashMap<>();
                                        businessToTechLink.put("source", businessId);
                                        businessToTechLink.put("target", node.get("id"));
                                        businessToTechLink.put("value", "技术应用");
                                        links.add(businessToTechLink);
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            
            // 检查项目备注或合作倾向中的关联
            for (ProjectEntity project : projects) {
                boolean foundInProject = false;
                
                // 检查合作倾向
                if (StringUtils.isNotEmpty(project.getCooperationTendency())) {
                    String companyCode = extractCompanyCode(project.getCooperationTendency());
                    if (company.getCompanyCode().equals(companyCode)) {
                        foundInProject = true;
                    }
                }
                
                // 检查备注
                if (!foundInProject && StringUtils.isNotEmpty(project.getRemarks())) {
                    String companyCode = extractCompanyCode(project.getRemarks());
                    if (company.getCompanyCode().equals(companyCode)) {
                        foundInProject = true;
                    }
                }
                
                if (foundInProject) {
                    // 项目到企业的连接
                    Map<String, Object> projectToCompanyLink = new HashMap<>();
                    projectToCompanyLink.put("source", "project-" + project.getProjectCode());
                    projectToCompanyLink.put("target", "company-" + company.getCompanyCode());
                    projectToCompanyLink.put("value", "引用关联");
                    links.add(projectToCompanyLink);
                }
            }
            
            // 检查专家备注中的关联
            if (StringUtils.isNotEmpty(expert.getRemarks())) {
                String companyCode = extractCompanyCode(expert.getRemarks());
                if (company.getCompanyCode().equals(companyCode)) {
                    // 专家到企业的备注连接
                    Map<String, Object> expertToCompanyLink = new HashMap<>();
                    expertToCompanyLink.put("source", "expert-" + expert.getExpertCode());
                    expertToCompanyLink.put("target", "company-" + company.getCompanyCode());
                    expertToCompanyLink.put("value", "备注关联");
                    links.add(expertToCompanyLink);
                }
            }
        }
        
        // 6. 组装图谱数据
        Map<String, Object> chartData = new HashMap<>();
        chartData.put("nodes", nodes);
        chartData.put("links", links);
        chartData.put("categories", List.of(
            Map.of("name", "专家"),
            Map.of("name", "项目"),
            Map.of("name", "企业"),
            Map.of("name", "学科领域"),
            Map.of("name", "研究方向"),
            Map.of("name", "单位学院"),
            Map.of("name", "部门"),
            Map.of("name", "技术关键词"),
            Map.of("name", "行业"),
            Map.of("name", "企业类型"),
            Map.of("name", "业务关键词")
        ));
        
        return ResultData.build().success(chartData);
    }
    
    /**
     * 从文本中提取企业编号
     * 支持格式如："关联企业库QY-001"、"QY-001"、"QY-004（亿纬锂能）"等
     */
    private String extractCompanyCode(String text) {
        if (StringUtils.isEmpty(text)) {
            return null;
        }
        
        // 匹配格式为QY-数字的编号，或QY-数字（任意内容）的编号
        Pattern pattern = Pattern.compile("QY-\\d+(?:\\([^)]*\\))?");
        Matcher matcher = pattern.matcher(text);
        
        if (matcher.find()) {
            String match = matcher.group();
            // 如果匹配到了带括号的形式，需要提取纯编号部分
            if (match.contains("(")) {
                return match.substring(0, match.indexOf("("));
            }
            return match;
        }
        
        return null;
    }
    
    /**
     * 从文本中提取多个企业编号
     * 支持格式如："关联企业库 QY-001、QY-004"
     */
    private List<String> extractMultipleCompanyCodes(String text) {
        if (StringUtils.isEmpty(text)) {
            return new ArrayList<>();
        }
        
        List<String> companyCodes = new ArrayList<>();
        // 匹配格式为QY-数字的编号
        Pattern pattern = Pattern.compile("QY-\\d+");
        Matcher matcher = pattern.matcher(text);
        
        while (matcher.find()) {
            companyCodes.add(matcher.group());
        }
        
        return companyCodes;
    }
    
    /**
     * 按照详细流程进行关联查询
     * 1. 通过专家姓名和编号查询专家信息
     * 2. 使用专家编码查询项目信息
     * 3. 从项目备注中提取企业编号
     * 4. 使用企业编号和专家编码查询企业信息
     */
    @ApiOperation(value = "详细关联查询")
    @GetMapping("/detailedQuery")
    @ResponseBody
    public ResultData detailedQuery(
            @RequestParam(value = "expertName", required = false) String expertName,
            @RequestParam(value = "expertCode", required = false) String expertCode) {
        
        // 验证查询参数
        if (StringUtils.isEmpty(expertName) && StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家姓名和编号不能同时为空");
        }
        
        // 1. 查询专家信息
        ExpertEntity expert = null;
        if (StringUtils.isNotEmpty(expertCode)) {
            expert = expertService.getByExpertCode(expertCode);
        }
        
        if (expert == null && StringUtils.isNotEmpty(expertName)) {
            // 如果没有通过编号找到，尝试通过姓名查找
            expert = expertService.getByExpertName(expertName);
        }
        
        if (expert == null) {
            return ResultData.build().error("未找到符合条件的专家信息");
        }
        
        // 2. 使用专家编码查询项目信息
        String finalExpertCode = expert.getExpertCode();
        List<ProjectEntity> projects = projectService.getByExpertCode(finalExpertCode);
        
        if (projects.isEmpty()) {
            return ResultData.build().error("未找到该专家关联的项目信息");
        }
        
        // 3. 从项目备注中提取企业编号
        Set<String> companyCodes = new HashSet<>();
        for (ProjectEntity project : projects) {
            if (StringUtils.isNotEmpty(project.getRemarks())) {
                companyCodes.addAll(extractMultipleCompanyCodes(project.getRemarks()));
            }
        }
        
        if (companyCodes.isEmpty()) {
            return ResultData.build().error("未从项目备注中找到企业编号信息");
        }
        
        // 4. 使用企业编号和专家编码查询匹配的企业信息
        List<CompanyEntity> matchedCompanies = new ArrayList<>();
        for (String companyCode : companyCodes) {
            CompanyEntity company = companyService.getByCompanyCode(companyCode);
            if (company != null) {
                // 检查该企业是否与当前专家关联
                if (StringUtils.isNotEmpty(company.getMatchedExpertCodes()) && 
                    company.getMatchedExpertCodes().contains(finalExpertCode)) {
                    matchedCompanies.add(company);
                }
            }
        }
        
        // 5. 组装返回数据
        Map<String, Object> result = new HashMap<>();
        result.put("expert", expert);
        result.put("projects", projects);
        result.put("matchedCompanies", matchedCompanies);
        
        // 生成对应的SQL语句展示(仅用于示例)
        String sql1 = "-- 1. 通过专家姓名和编号查询专家信息\n" +
                "SELECT * FROM expert_info WHERE name = '" + expertName + "' OR expert_code = '" + expertCode + "' LIMIT 1;";
        
        String sql2 = "-- 2. 使用专家编码查询项目信息\n" +
                "SELECT * FROM project_info WHERE leader_code = '" + finalExpertCode + "' OR participant_experts LIKE '%" + finalExpertCode + "%';";
        
        String sql3 = "-- 3. 从项目备注中提取企业编号后查询企业信息\n" +
                "SELECT * FROM company_info WHERE company_code IN (";
        
        sql3 += String.join(", ", companyCodes.stream().map(code -> "'" + code + "'").toArray(String[]::new));
        sql3 += ");";
        
        String sql4 = "-- 4. 筛选与专家匹配的企业\n" +
                "SELECT * FROM company_info WHERE matched_expert_codes LIKE '%" + finalExpertCode + "%' AND company_code IN (";
        sql4 += String.join(", ", companyCodes.stream().map(code -> "'" + code + "'").toArray(String[]::new));
        sql4 += ");";
        
        result.put("sqlExamples", List.of(sql1, sql2, sql3, sql4));
        
        return ResultData.build().success(result);
    }
    
    /**
     * 专家综合编辑页面
     */
    @ApiOperation(value = "专家综合编辑页面")
    @GetMapping("/edit")
    public String editRelations(@RequestParam("expertCode") String expertCode) {
        return "/expertmanage/relation/edit";
    }
    
    /**
     * 获取专家综合编辑数据
     */
    @ApiOperation(value = "获取专家综合编辑数据")
    @GetMapping("/getRelationsForEdit")
    @ResponseBody
    public ResultData getRelationsForEdit(@RequestParam("expertCode") String expertCode) {
        if (StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家编号不能为空");
        }
        
        // 1. 获取专家信息
        ExpertEntity expert = expertService.getByExpertCode(expertCode);
        if (expert == null) {
            return ResultData.build().error("未找到对应的专家信息");
        }
        
        // 2. 获取专家相关的项目
        List<ProjectEntity> projects = projectService.getByExpertCode(expertCode);
        
        // 3. 对每个项目，提取并关联企业信息
        List<Map<String, Object>> relationData = new ArrayList<>();
        Set<String> processedCompanyCodes = new HashSet<>(); // 跟踪已处理的企业编号以避免重复
        
        // 处理项目关联
        for (ProjectEntity project : projects) {
            // 从项目备注或合作倾向中提取企业编号
            Set<String> companyCodes = new HashSet<>();
            
            // 从项目合作企业倾向中提取企业编号
            if (StringUtils.isNotEmpty(project.getCooperationTendency())) {
                String companyCode = extractCompanyCode(project.getCooperationTendency());
                if (companyCode != null) {
                    companyCodes.add(companyCode);
                }
            }
            
            // 从项目备注中提取企业编号
            if (StringUtils.isNotEmpty(project.getRemarks())) {
                String companyCode = extractCompanyCode(project.getRemarks());
                if (companyCode != null) {
                    companyCodes.add(companyCode);
                }
            }
            
            // 如果找到企业编号，查询企业信息并添加到关联数据中
            if (!companyCodes.isEmpty()) {
                for (String companyCode : companyCodes) {
                    CompanyEntity company = companyService.getByCompanyCode(companyCode);
                    if (company != null) {
                        Map<String, Object> relationMap = new HashMap<>();
                        relationMap.put("project", project);
                        relationMap.put("company", company);
                        relationMap.put("relationType", "项目-企业关联");
                        relationData.add(relationMap);
                        processedCompanyCodes.add(companyCode);
                    }
                }
            } else {
                // 如果没有找到关联企业，仍然添加项目信息
                Map<String, Object> relationMap = new HashMap<>();
                relationMap.put("project", project);
                relationMap.put("relationType", "专家-项目关联");
                relationData.add(relationMap);
            }
        }
        
        // 4. 检查专家的备注中是否包含企业信息
        if (StringUtils.isNotEmpty(expert.getRemarks())) {
            String companyCode = extractCompanyCode(expert.getRemarks());
            if (companyCode != null && !processedCompanyCodes.contains(companyCode)) {
                CompanyEntity company = companyService.getByCompanyCode(companyCode);
                if (company != null) {
                    Map<String, Object> remarksRelationMap = new HashMap<>();
                    remarksRelationMap.put("company", company);
                    remarksRelationMap.put("relationType", "专家-企业备注关联");
                    relationData.add(remarksRelationMap);
                }
            }
        }
        
        // 5. 组装返回数据
        Map<String, Object> result = new HashMap<>();
        result.put("expert", expert);
        result.put("relations", relationData);
        
        return ResultData.build().success(result);
    }
    
    /**
     * 保存专家综合编辑数据
     */
    @ApiOperation(value = "保存专家综合编辑数据")
    @PostMapping("/saveRelations")
    @ResponseBody
    @Transactional
    public ResultData saveRelations(
            @RequestBody Map<String, Object> editData) {
        try {
            // 1. 保存专家信息
            ExpertEntity expert = new ExpertEntity();
            Map<String, Object> expertMap = (Map<String, Object>) editData.get("expert");
            if (expertMap != null) {
                // 将Map转换为ExpertEntity对象
                String expertJson = new com.alibaba.fastjson.JSONObject(expertMap).toJSONString();
                expert = com.alibaba.fastjson.JSON.parseObject(expertJson, ExpertEntity.class);
                expertService.updateById(expert);
            }
            
            // 2. 保存项目信息
            List<Map<String, Object>> projects = (List<Map<String, Object>>) editData.get("projects");
            if (projects != null && !projects.isEmpty()) {
                for (Map<String, Object> projectMap : projects) {
                    String projectJson = new com.alibaba.fastjson.JSONObject(projectMap).toJSONString();
                    ProjectEntity project = com.alibaba.fastjson.JSON.parseObject(projectJson, ProjectEntity.class);
                    projectService.updateById(project);
                }
            }
            
            // 3. 保存企业信息
            List<Map<String, Object>> companies = (List<Map<String, Object>>) editData.get("companies");
            if (companies != null && !companies.isEmpty()) {
                for (Map<String, Object> companyMap : companies) {
                    String companyJson = new com.alibaba.fastjson.JSONObject(companyMap).toJSONString();
                    CompanyEntity company = com.alibaba.fastjson.JSON.parseObject(companyJson, CompanyEntity.class);
                    companyService.updateById(company);
                }
            }
            
            return ResultData.build().success("保存成功");
        } catch (Exception e) {
            return ResultData.build().error("保存失败：" + e.getMessage());
        }
    }

    /**
     * 下载Excel导入模板
     * @param type 模板类型：expert（专家模板）、project（项目模板）、company（企业模板）
     * @param response HTTP响应对象
     */
    @ApiOperation(value = "下载Excel导入模板")
    @GetMapping("/importTemplate/{type}")
    public void downloadImportTemplate(@PathVariable("type") String type, HttpServletResponse response) {
        Workbook workbook;
        String fileName;
        
        try {
            // 创建Excel工作簿
            workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            
            // 创建样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            
            // 创建标题行
            Row headerRow = sheet.createRow(0);
            
            // 根据不同类型创建不同的模板
            if ("expert".equals(type)) {
                fileName = "专家信息导入模板.xlsx";
                
                // 设置列头 - 与expert_info表结构保持一致
                String[] headers = {
                    "序号", "专家编号*", "姓名*", "性别", "年龄", "职称/头衔", "最高学历", 
                    "一级学科", "二级学科", "所属学院", "校内/校外标识", "研究方向关键词", 
                    "代表性成果类型", "知识产权持有量", "社会职务", "纵向项目数", 
                    "横向项目数", "联系方式", "备注"
                };
                
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headers[i]);
                    cell.setCellStyle(headerStyle);
                }
                
                // 添加样例数据 - 从数据库导入数据
                // 第一行样例数据
                Row dataRow1 = sheet.createRow(1);
                dataRow1.createCell(0).setCellValue(1);
                dataRow1.createCell(1).setCellValue("ZJ-001");
                dataRow1.createCell(2).setCellValue("张三");
                dataRow1.createCell(3).setCellValue("男");
                dataRow1.createCell(4).setCellValue(45);
                dataRow1.createCell(5).setCellValue("教授");
                dataRow1.createCell(6).setCellValue("博士");
                dataRow1.createCell(7).setCellValue("材料科学与工程");
                dataRow1.createCell(8).setCellValue("新能源材料");
                dataRow1.createCell(9).setCellValue("材料学院");
                dataRow1.createCell(10).setCellValue("校内");
                dataRow1.createCell(11).setCellValue("锂电池、固态电解质、电催化");
                dataRow1.createCell(12).setCellValue("专利转化 / 论文");
                dataRow1.createCell(13).setCellValue("发明专利 5 项");
                dataRow1.createCell(14).setCellValue("格林美（荆门）技术顾问");
                dataRow1.createCell(15).setCellValue("国家级 2 项");
                dataRow1.createCell(16).setCellValue("企业合作 3 项");
                dataRow1.createCell(17).setCellValue("zhangsan@hust.edu");
                dataRow1.createCell(18).setCellValue("项目库 Z-001 负责人");
                
                // 第二行样例数据
                Row dataRow2 = sheet.createRow(2);
                dataRow2.createCell(0).setCellValue(2);
                dataRow2.createCell(1).setCellValue("ZJ-002");
                dataRow2.createCell(2).setCellValue("李四");
                dataRow2.createCell(3).setCellValue("女");
                dataRow2.createCell(4).setCellValue(38);
                dataRow2.createCell(5).setCellValue("副教授");
                dataRow2.createCell(6).setCellValue("博士");
                dataRow2.createCell(7).setCellValue("机械工程");
                dataRow2.createCell(8).setCellValue("智能装备");
                dataRow2.createCell(9).setCellValue("机械学院");
                dataRow2.createCell(10).setCellValue("校内");
                dataRow2.createCell(11).setCellValue("工业机器人、故障诊断、自动化");
                dataRow2.createCell(12).setCellValue("软件著作权 / 横向课题");
                dataRow2.createCell(13).setCellValue("软著 2 项");
                dataRow2.createCell(14).setCellValue("新洋丰（荆门）智能化顾问");
                dataRow2.createCell(15).setCellValue("省部级 1 项");
                dataRow2.createCell(16).setCellValue("企业合作 5 项");
                dataRow2.createCell(17).setCellValue("lisi@jmtc.edu");
                dataRow2.createCell(18).setCellValue("项目库 Z-002 负责人");
                
                // 第三行样例数据
                Row dataRow3 = sheet.createRow(3);
                dataRow3.createCell(0).setCellValue(3);
                dataRow3.createCell(1).setCellValue("ZJ-003");
                dataRow3.createCell(2).setCellValue("王五");
                dataRow3.createCell(3).setCellValue("男");
                dataRow3.createCell(4).setCellValue(52);
                dataRow3.createCell(5).setCellValue("研究员");
                dataRow3.createCell(6).setCellValue("博士");
                dataRow3.createCell(7).setCellValue("生物医学工程");
                dataRow3.createCell(8).setCellValue("生物医药");
                dataRow3.createCell(9).setCellValue("校外单位");
                dataRow3.createCell(10).setCellValue("校外");
                dataRow3.createCell(11).setCellValue("靶向药物、纳米载体、合成化学");
                dataRow3.createCell(12).setCellValue("成果转化 / 企业委托");
                dataRow3.createCell(13).setCellValue("国际专利 1 项");
                dataRow3.createCell(14).setCellValue("健帆生物（荆门）独立董事");
                dataRow3.createCell(15).setCellValue("-");
                dataRow3.createCell(16).setCellValue("企业合作 2 项");
                dataRow3.createCell(17).setCellValue("wangwu@opticvalley.com");
                dataRow3.createCell(18).setCellValue("项目库 Z-003 负责人");
                
                // 设置列宽
                for (int i = 0; i < headers.length; i++) {
                    sheet.setColumnWidth(i, 4000);
                }
                sheet.setColumnWidth(11, 6000); // 研究方向关键词列宽
                sheet.setColumnWidth(12, 5000); // 成果类型列宽
                sheet.setColumnWidth(18, 6000); // 备注列宽
                
            } else if ("project".equals(type)) {
                fileName = "项目信息导入模板.xlsx";
                
                // 设置列头 - 与project_info表结构保持一致
                String[] headers = {
                    "序号", "项目编号*", "项目名称*", "所属专业所", "负责人编号*", "参与专家编号", 
                    "项目类型", "技术成熟度（TRL）", "核心技术关键词", "应用行业", "市场化阶段", 
                    "转化方式", "知识产权详情", "合作企业意向", "预算需求", "联系方式", "备注"
                };
                
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headers[i]);
                    cell.setCellStyle(headerStyle);
                }
                
                // 第一行样例数据
                Row dataRow1 = sheet.createRow(1);
                dataRow1.createCell(0).setCellValue(1);
                dataRow1.createCell(1).setCellValue("Z-001");
                dataRow1.createCell(2).setCellValue("高能量密度固态锂电池");
                dataRow1.createCell(3).setCellValue("新能源材料所");
                dataRow1.createCell(4).setCellValue("ZJ-001");
                dataRow1.createCell(5).setCellValue("ZJ-001, ZJ-005");
                dataRow1.createCell(6).setCellValue("产品类");
                dataRow1.createCell(7).setCellValue("TRL7（中试阶段）");
                dataRow1.createCell(8).setCellValue("固态电解质、界面改性");
                dataRow1.createCell(9).setCellValue("新能源汽车 / 储能（对应企业库行业分类）");
                dataRow1.createCell(10).setCellValue("中试阶段");
                dataRow1.createCell(11).setCellValue("技术转让 / 作价入股");
                dataRow1.createCell(12).setCellValue("发明专利 3 项（授权 2 项，申请 1 项）");
                dataRow1.createCell(13).setCellValue("QY-001（格林美）, QY-004（亿纬锂能）");
                dataRow1.createCell(14).setCellValue("2000 万元");
                dataRow1.createCell(15).setCellValue("zhangsan@hust.edu");
                dataRow1.createCell(16).setCellValue("关联企业库 QY-001、QY-004");
                
                // 第二行样例数据
                Row dataRow2 = sheet.createRow(2);
                dataRow2.createCell(0).setCellValue(2);
                dataRow2.createCell(1).setCellValue("Z-002");
                dataRow2.createCell(2).setCellValue("工业机器人故障诊断系统");
                dataRow2.createCell(3).setCellValue("智能装备所");
                dataRow2.createCell(4).setCellValue("ZJ-002");
                dataRow2.createCell(5).setCellValue("ZJ-002, ZJ-006");
                dataRow2.createCell(6).setCellValue("服务类");
                dataRow2.createCell(7).setCellValue("TRL9（商业化）");
                dataRow2.createCell(8).setCellValue("机器学习、故障预测");
                dataRow2.createCell(9).setCellValue("机械制造 / 电子加工");
                dataRow2.createCell(10).setCellValue("商业化运营");
                dataRow2.createCell(11).setCellValue("软件许可 / 技术服务");
                dataRow2.createCell(12).setCellValue("软著 2 项（已授权）");
                dataRow2.createCell(13).setCellValue("QY-002（新洋丰）, QY-003（长源动力）");
                dataRow2.createCell(14).setCellValue("500 万元");
                dataRow2.createCell(15).setCellValue("lisi@jmtc.edu");
                dataRow2.createCell(16).setCellValue("关联企业库 QY-002、QY-003");
                
                // 第三行样例数据
                Row dataRow3 = sheet.createRow(3);
                dataRow3.createCell(0).setCellValue(3);
                dataRow3.createCell(1).setCellValue("Z-003");
                dataRow3.createCell(2).setCellValue("抗肿瘤纳米靶向药物中间体");
                dataRow3.createCell(3).setCellValue("生物医药所");
                dataRow3.createCell(4).setCellValue("ZJ-003");
                dataRow3.createCell(5).setCellValue("ZJ-003, ZJ-007");
                dataRow3.createCell(6).setCellValue("产品类");
                dataRow3.createCell(7).setCellValue("TRL5（样机阶段）");
                dataRow3.createCell(8).setCellValue("纳米载体、靶向递送");
                dataRow3.createCell(9).setCellValue("制药 / CRO（对应企业库行业分类）");
                dataRow3.createCell(10).setCellValue("实验室阶段");
                dataRow3.createCell(11).setCellValue("技术许可 / 联合开发");
                dataRow3.createCell(12).setCellValue("PCT 国际专利 1 项（申请中）");
                dataRow3.createCell(13).setCellValue("QY-005（健帆生物）");
                dataRow3.createCell(14).setCellValue("800 万元");
                dataRow3.createCell(15).setCellValue("wangwu@opticvalley.com");
                dataRow3.createCell(16).setCellValue("关联企业库 QY-005");
                
                // 设置列宽
                for (int i = 0; i < headers.length; i++) {
                    sheet.setColumnWidth(i, 4000);
                }
                sheet.setColumnWidth(2, 6000);  // 项目名称列宽
                sheet.setColumnWidth(8, 5000);  // 核心技术关键词列宽
                sheet.setColumnWidth(9, 6000);  // 应用行业列宽
                sheet.setColumnWidth(12, 6000); // 知识产权详情列宽
                sheet.setColumnWidth(16, 6000); // 备注列宽
                
            } else if ("company".equals(type)) {
                fileName = "企业信息导入模板.xlsx";
                
                // 设置列头 - 与company_info表结构保持一致
                String[] headers = {
                    "序号", "企业编号*", "企业名称*", "企业类型", "所属行业（一级）", "所属行业（二级）", 
                    "注册地址", "联系人", "联系方式", "主营业务关键词", "合作需求类型", 
                    "需求技术领域", "需求专家职称", "对接专家编号", "合作状态", "合作历史项目", "备注"
                };
                
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headers[i]);
                    cell.setCellStyle(headerStyle);
                }
                
                // 第一行样例数据
                Row dataRow1 = sheet.createRow(1);
                dataRow1.createCell(0).setCellValue(1);
                dataRow1.createCell(1).setCellValue("QY-001");
                dataRow1.createCell(2).setCellValue("格林美");
                dataRow1.createCell(3).setCellValue("本地规上企业");
                dataRow1.createCell(4).setCellValue("新能源");
                dataRow1.createCell(5).setCellValue("动力电池回收");
                dataRow1.createCell(6).setCellValue("掇刀区");
                dataRow1.createCell(7).setCellValue("刘经理");
                dataRow1.createCell(8).setCellValue("0724-8888888");
                dataRow1.createCell(9).setCellValue("电池回收、材料再生");
                dataRow1.createCell(10).setCellValue("技术合作 / 成果转化");
                dataRow1.createCell(11).setCellValue("新能源材料、固态电解质");
                dataRow1.createCell(12).setCellValue("教授 / 副教授");
                dataRow1.createCell(13).setCellValue("ZJ-001, ZJ-005");
                dataRow1.createCell(14).setCellValue("深度合作");
                dataRow1.createCell(15).setCellValue("Z-001, Z-009");
                dataRow1.createCell(16).setCellValue("专家库 ZJ-001 为技术顾问");
                
                // 第二行样例数据
                Row dataRow2 = sheet.createRow(2);
                dataRow2.createCell(0).setCellValue(2);
                dataRow2.createCell(1).setCellValue("QY-002");
                dataRow2.createCell(2).setCellValue("新洋丰");
                dataRow2.createCell(3).setCellValue("本地龙头企业");
                dataRow2.createCell(4).setCellValue("化工 / 农业");
                dataRow2.createCell(5).setCellValue("化肥制造");
                dataRow2.createCell(6).setCellValue("东宝区");
                dataRow2.createCell(7).setCellValue("陈总监");
                dataRow2.createCell(8).setCellValue("0724-6666666");
                dataRow2.createCell(9).setCellValue("化肥生产、土壤修复");
                dataRow2.createCell(10).setCellValue("技术咨询 / 人才招聘");
                dataRow2.createCell(11).setCellValue("智能装备、自动化控制");
                dataRow2.createCell(12).setCellValue("副教授 / 博士");
                dataRow2.createCell(13).setCellValue("ZJ-002, ZJ-006");
                dataRow2.createCell(14).setCellValue("意向对接");
                dataRow2.createCell(15).setCellValue("-");
                dataRow2.createCell(16).setCellValue("需求匹配专家库 ZJ-002");
                
                // 第三行样例数据
                Row dataRow3 = sheet.createRow(3);
                dataRow3.createCell(0).setCellValue(3);
                dataRow3.createCell(1).setCellValue("QY-005");
                dataRow3.createCell(2).setCellValue("健帆生物");
                dataRow3.createCell(3).setCellValue("友好企业");
                dataRow3.createCell(4).setCellValue("生物医药");
                dataRow3.createCell(5).setCellValue("靶向药物中间体");
                dataRow3.createCell(6).setCellValue("市外（武汉）");
                dataRow3.createCell(7).setCellValue("王部长");
                dataRow3.createCell(8).setCellValue("027-87180000");
                dataRow3.createCell(9).setCellValue("医药中间体合成、纳米技术");
                dataRow3.createCell(10).setCellValue("联合研发 / 成果转化");
                dataRow3.createCell(11).setCellValue("生物医药、纳米载体");
                dataRow3.createCell(12).setCellValue("研究员 / 博士");
                dataRow3.createCell(13).setCellValue("ZJ-003, ZJ-007");
                dataRow3.createCell(14).setCellValue("签约合作");
                dataRow3.createCell(15).setCellValue("Z-003, Z-010");
                dataRow3.createCell(16).setCellValue("项目库 Z-003 合作企业");
                
                // 设置列宽
                for (int i = 0; i < headers.length; i++) {
                    sheet.setColumnWidth(i, 4000);
                }
                sheet.setColumnWidth(2, 5000);  // 企业名称列宽
                sheet.setColumnWidth(9, 5000);  // 主营业务关键词列宽
                sheet.setColumnWidth(11, 5000); // 需求技术领域列宽
                sheet.setColumnWidth(16, 6000); // 备注列宽
                
            } else {
                response.setContentType("text/html;charset=utf-8");
                response.getWriter().write("不支持的模板类型");
                return;
            }
            
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            
            // 输出到响应流
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();
            
        } catch (Exception e) {
            LOG.error("生成导入模板失败", e);
            try {
                response.setContentType("text/html;charset=utf-8");
                response.getWriter().write("生成模板失败：" + e.getMessage());
            } catch (IOException ex) {
                LOG.error("输出错误信息失败", ex);
            }
        }
    }
    
    /**
     * 跳转到导入页面
     * @return 导入页面路径
     */
    @ApiOperation("导入数据页面")
    @GetMapping("/import")
    public String importPage() {
        return "/expertmanage/relation/import-fixed";
    }

    /**
     * 上传Excel文件到Session
     * @param file 上传的Excel文件
     * @param type 数据类型（expert, project, company）
     * @return 上传结果
     */
    @ApiOperation("上传Excel文件")
    @PostMapping("/uploadExcel")
    @ResponseBody
    public ResultData uploadExcel(@RequestParam("file") MultipartFile file, @RequestParam("type") String type, HttpServletRequest request) {
        if (file.isEmpty()) {
            return ResultData.build().error("文件为空");
        }
        
        if (!type.equals("expert") && !type.equals("project") && !type.equals("company")) {
            return ResultData.build().error("不支持的数据类型");
        }
        
        try {
            // 将文件存储在session中，供后续处理
            HttpSession session = request.getSession();
            Map<String, MultipartFile> files = getSessionFiles(session);
            files.put(type, file);
            session.setAttribute("IMPORT_FILES", files);
            
            return ResultData.build().success("文件上传成功");
        } catch (Exception e) {
            LOG.error("上传Excel文件失败", e);
            return ResultData.build().error("上传失败: " + e.getMessage());
        }
    }

    /**
     * 预览Excel数据
     * @return 预览数据
     */
    @ApiOperation("预览Excel数据")
    @GetMapping("/previewExcel")
    @ResponseBody
    public ResultData previewExcel(HttpServletRequest request) {
        try {
            HttpSession session = request.getSession();
            Map<String, MultipartFile> files = getSessionFiles(session);
            
            Map<String, Object> result = new HashMap<>();
            Map<String, List<Map<String, Object>>> previewData = new HashMap<>();
            
            // 处理专家数据
            if (files.containsKey("expert")) {
                List<Map<String, Object>> expertData = parseExcelData(files.get("expert"), "expert");
                previewData.put("expert", expertData);
            } else {
                previewData.put("expert", new ArrayList<>());
            }
            
            // 处理项目数据
            if (files.containsKey("project")) {
                List<Map<String, Object>> projectData = parseExcelData(files.get("project"), "project");
                previewData.put("project", projectData);
            } else {
                previewData.put("project", new ArrayList<>());
            }
            
            // 处理企业数据
            if (files.containsKey("company")) {
                List<Map<String, Object>> companyData = parseExcelData(files.get("company"), "company");
                previewData.put("company", companyData);
            } else {
                previewData.put("company", new ArrayList<>());
            }
            
            result.put("preview", previewData);
            return ResultData.build().success(result);
        } catch (Exception e) {
            LOG.error("预览Excel数据失败", e);
            return ResultData.build().error("预览失败: " + e.getMessage());
        }
    }

    /**
     * 从Session获取上传文件Map
     * @param session HTTP会话
     * @return 文件Map
     */
    @SuppressWarnings("unchecked")
    private Map<String, MultipartFile> getSessionFiles(HttpSession session) {
        Map<String, MultipartFile> files = (Map<String, MultipartFile>) session.getAttribute("IMPORT_FILES");
        if (files == null) {
            files = new HashMap<>();
        }
        return files;
    }

    /**
     * 解析Excel数据
     * @param file Excel文件
     * @param type 数据类型
     * @return 解析后的数据列表
     */
    private List<Map<String, Object>> parseExcelData(MultipartFile file, String type) throws Exception {
        List<Map<String, Object>> dataList = new ArrayList<>();
        
        try (InputStream is = file.getInputStream()) {
            Workbook workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取标题行
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    headers.add(cell.getStringCellValue().trim());
                }
            }
            
            LOG.info("解析到的标题行: " + headers);
            
            // 解析数据行 (从第2行开始，跳过标题行)
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                
                Map<String, Object> rowData = new HashMap<>();
                boolean isEmptyRow = true;
                
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String value = "";
                    
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    value = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                                } else {
                                    // 保留为数值，防止转换为科学计数法
                                    double numValue = cell.getNumericCellValue();
                                    if (numValue == Math.floor(numValue)) {
                                        value = String.valueOf((int) numValue);
                                    } else {
                                        value = String.valueOf(numValue);
                                    }
                                }
                                break;
                            case BOOLEAN:
                                value = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                try {
                                    value = String.valueOf(cell.getNumericCellValue());
                                } catch (Exception e) {
                                    value = cell.getStringCellValue();
                                }
                                break;
                            default:
                                value = "";
                        }
                    }
                    
                    if (StringUtils.isNotBlank(value)) {
                        isEmptyRow = false;
                    }
                    
                    if (j < headers.size()) {
                    rowData.put(headers.get(j), value);
                    }
                }
                
                if (!isEmptyRow) {
                    // 验证数据
                    boolean isValid = validateData(rowData, type);
                    rowData.put("_valid", isValid);
                    if (!isValid) {
                        LOG.warn("无效数据行 " + (i+1) + ": " + rowData);
                        rowData.put("_error", "数据格式不正确或必填字段为空");
                    } else {
                        LOG.info("有效数据行 " + (i+1) + ": " + rowData);
                    }
                    
                    dataList.add(rowData);
                }
            }
            
            LOG.info("共解析到 " + dataList.size() + " 条数据，其中有效数据: " + 
                    dataList.stream().filter(d -> (boolean)d.get("_valid")).count() + " 条");
        }
        
        return dataList;
    }

    /**
     * 验证导入数据
     * @param data 数据行
     * @param type 数据类型
     * @return 是否有效
     */
    private boolean validateData(Map<String, Object> data, String type) {
        LOG.info("验证" + type + "数据: " + data);
        
        switch (type) {
            case "expert":
                // 检查专家必填字段
                String expertCode = getStringValue(data, "专家编号", "专家编号*", "expertCode");
                String name = getStringValue(data, "姓名", "姓名*", "name");
                
                return StringUtils.isNotBlank(expertCode) && StringUtils.isNotBlank(name);
                
            case "project":
                // 检查项目必填字段
                String projectCode = getStringValue(data, "项目编号", "项目编号*", "projectCode");
                String projectName = getStringValue(data, "项目名称", "项目名称*", "projectName");
                
                return StringUtils.isNotBlank(projectCode) && StringUtils.isNotBlank(projectName);
                
            case "company":
                // 检查企业必填字段
                String companyCode = getStringValue(data, "企业编号", "企业编号*", "companyCode");
                String companyName = getStringValue(data, "企业名称", "企业名称*", "companyName");
                
                return StringUtils.isNotBlank(companyCode) && StringUtils.isNotBlank(companyName);
                
            default:
                return false;
        }
    }
    
    /**
     * 从数据中获取字符串值，支持多个可能的键名
     * @param data 数据映射
     * @param possibleKeys 可能的键名数组
     * @return 找到的值，如果未找到则返回空字符串
     */
    private String getStringValue(Map<String, Object> data, String... possibleKeys) {
        for (String key : possibleKeys) {
            if (data.containsKey(key) && data.get(key) != null) {
                String value = data.get(key).toString();
                if (StringUtils.isNotBlank(value)) {
                    return value;
                }
            }
        }
        return "";
    }

    /**
     * 导入Excel数据
     * @return 导入结果
     */
    @ApiOperation("导入Excel数据")
    @PostMapping("/importExcel")
    @ResponseBody
    @Transactional
    public ResultData importExcel(HttpServletRequest request) {
        try {
            HttpSession session = request.getSession();
            Map<String, MultipartFile> files = getSessionFiles(session);
            
            if (files.isEmpty()) {
                return ResultData.build().error("没有找到要导入的文件");
            }
            
            List<String> details = new ArrayList<>();
            
            // 导入专家数据
            if (files.containsKey("expert")) {
                List<Map<String, Object>> expertData = parseExcelData(files.get("expert"), "expert");
                int expertCount = importExpertData(expertData);
                details.add("成功导入 " + expertCount + " 条专家信息");
            }
            
            // 导入项目数据
            if (files.containsKey("project")) {
                List<Map<String, Object>> projectData = parseExcelData(files.get("project"), "project");
                int projectCount = importProjectData(projectData);
                details.add("成功导入 " + projectCount + " 条项目信息");
            }
            
            // 导入企业数据
            if (files.containsKey("company")) {
                List<Map<String, Object>> companyData = parseExcelData(files.get("company"), "company");
                int companyCount = importCompanyData(companyData);
                details.add("成功导入 " + companyCount + " 条企业信息");
            }
            
            // 清除Session中的上传文件
            session.removeAttribute("IMPORT_FILES");
            
            Map<String, Object> result = new HashMap<>();
            result.put("details", details);
            
            return ResultData.build().success(result);
        } catch (Exception e) {
            LOG.error("导入Excel数据失败", e);
            return ResultData.build().error("导入失败: " + e.getMessage());
        }
    }

    /**
     * 导入专家数据
     * @param dataList 数据列表
     * @return 导入数量
     */
    private int importExpertData(List<Map<String, Object>> dataList) {
        int count = 0;
        
        for (Map<String, Object> data : dataList) {
            if (!(Boolean) data.get("_valid")) {
                continue;
            }
            
            ExpertEntity expert = new ExpertEntity();
            
            // 序号
            String serialNoStr = getStringValue(data, "序号");
            if (StringUtils.isNotBlank(serialNoStr)) {
                try {
                    expert.setSerialNo(Integer.parseInt(serialNoStr));
                } catch (NumberFormatException e) {
                    LOG.warn("序号格式不正确: " + serialNoStr);
                }
            }
            
            // 基本信息
            expert.setExpertCode(getStringValue(data, "专家编号", "专家编号*"));
            expert.setName(getStringValue(data, "姓名", "姓名*"));
            expert.setGender(getStringValue(data, "性别"));
            
            String ageStr = getStringValue(data, "年龄");
            if (StringUtils.isNotBlank(ageStr)) {
                try {
                    expert.setAge(Integer.parseInt(ageStr));
                } catch (NumberFormatException e) {
                    LOG.warn("年龄格式不正确: " + ageStr);
                }
            }
            
            // 职称和学历
            expert.setTitle(getStringValue(data, "职称/头衔", "职称", "头衔"));
            expert.setHighestEducation(getStringValue(data, "最高学历", "学历"));
            
            // 学科和单位
            expert.setFirstLevelDiscipline(getStringValue(data, "一级学科", "一级学科*", "第一级学科", "第一级学科*"));
            expert.setSecondLevelDiscipline(getStringValue(data, "二级学科", "二级学科*", "第二级学科", "第二级学科*"));
            expert.setCollege(getStringValue(data, "所属学院", "所属学院/部门"));
            expert.setInsideOutside(getStringValue(data, "校内/校外标识"));
            
            // 研究方向
            expert.setResearchKeywords(getStringValue(data, "研究方向关键词", "研究方向关键词*"));
            
            // 成果和项目
            expert.setAchievementType(getStringValue(data, "代表性成果类型"));
            expert.setIpOwnership(getStringValue(data, "知识产权持有量"));
            expert.setSocialPositions(getStringValue(data, "社会职务"));
            expert.setVerticalProjects(getStringValue(data, "纵向项目数"));
            expert.setHorizontalProjects(getStringValue(data, "横向项目数"));
            
            // 联系方式和备注
            expert.setContact(getStringValue(data, "联系方式", "联系电话", "电子邮箱"));
            expert.setRemarks(getStringValue(data, "备注"));
            
            LOG.info("准备保存专家: " + expert.getExpertCode() + " - " + expert.getName());
            
            // 检查是否存在，存在则更新，不存在则新增
            ExpertEntity existingExpert = expertService.getByExpertCode(expert.getExpertCode());
            if (existingExpert != null) {
                expert.setId(existingExpert.getId());
                expertService.updateById(expert);
                LOG.info("更新专家: " + expert.getExpertCode());
            } else {
                expertService.save(expert);
                LOG.info("新增专家: " + expert.getExpertCode());
            }
            
            count++;
        }
        
        return count;
    }

    /**
     * 导入项目数据
     * @param dataList 数据列表
     * @return 导入数量
     */
    private int importProjectData(List<Map<String, Object>> dataList) {
        int count = 0;
        
        for (Map<String, Object> data : dataList) {
            if (!(Boolean) data.get("_valid")) {
                continue;
            }
            
            ProjectEntity project = new ProjectEntity();
            
            // 序号
            String serialNoStr = getStringValue(data, "序号");
            if (StringUtils.isNotBlank(serialNoStr)) {
                try {
                    project.setSerialNo(Integer.parseInt(serialNoStr));
                } catch (NumberFormatException e) {
                    LOG.warn("序号格式不正确: " + serialNoStr);
                }
            }
            
            // 基本信息
            project.setProjectCode(getStringValue(data, "项目编号", "项目编号*"));
            project.setProjectName(getStringValue(data, "项目名称", "项目名称*"));
            project.setDepartment(getStringValue(data, "所属专业所", "所属专业所/部门", "所属专业所/部门*"));
            project.setLeaderCode(getStringValue(data, "负责人编号", "负责人编号*"));
            project.setParticipantExperts(getStringValue(data, "参与专家编号"));
            
            // 项目类型和技术信息
            project.setProjectType(getStringValue(data, "项目类型", "项目类型*"));
            project.setTechMaturity(getStringValue(data, "技术成熟度（TRL）", "技术成熟度"));
            project.setCoreTechKeywords(getStringValue(data, "核心技术关键词", "技术关键词"));
            project.setApplicationIndustry(getStringValue(data, "应用行业"));
            
            // 市场和转化信息
            project.setMarketStage(getStringValue(data, "市场化阶段", "项目状态"));
            project.setTransformationMode(getStringValue(data, "转化方式"));
            project.setIpDetails(getStringValue(data, "知识产权详情"));
            project.setCooperationTendency(getStringValue(data, "合作企业意向", "合作企业倾向"));
            
            // 预算和联系方式
            project.setBudget(getStringValue(data, "预算需求", "项目经费(万元)"));
            project.setContact(getStringValue(data, "联系方式"));
            project.setRemarks(getStringValue(data, "备注"));
            
            LOG.info("准备保存项目: " + project.getProjectCode() + " - " + project.getProjectName());
            
            // 检查是否存在，存在则更新，不存在则新增
            ProjectEntity existingProject = projectService.getByProjectCode(project.getProjectCode());
            if (existingProject != null) {
                project.setId(existingProject.getId());
                projectService.updateById(project);
                LOG.info("更新项目: " + project.getProjectCode());
            } else {
                projectService.save(project);
                LOG.info("新增项目: " + project.getProjectCode());
            }
            
            count++;
        }
        
        return count;
    }

    /**
     * 导入企业数据
     * @param dataList 数据列表
     * @return 导入数量
     */
    private int importCompanyData(List<Map<String, Object>> dataList) {
        int count = 0;
        
        for (Map<String, Object> data : dataList) {
            if (!(Boolean) data.get("_valid")) {
                continue;
            }
            
            CompanyEntity company = new CompanyEntity();
            
            // 序号
            String serialNoStr = getStringValue(data, "序号");
            if (StringUtils.isNotBlank(serialNoStr)) {
                try {
                    company.setSerialNo(Integer.parseInt(serialNoStr));
                } catch (NumberFormatException e) {
                    LOG.warn("序号格式不正确: " + serialNoStr);
                }
            }
            
            // 基本信息
            company.setCompanyCode(getStringValue(data, "企业编号", "企业编号*"));
            company.setCompanyName(getStringValue(data, "企业名称", "企业名称*"));
            company.setCompanyType(getStringValue(data, "企业类型"));
            
            // 行业信息
            company.setIndustryPrimary(getStringValue(data, "所属行业（一级）", "主要行业", "主要行业*"));
            company.setIndustrySecondary(getStringValue(data, "所属行业（二级）", "次要行业"));
            
            // 地址和联系人
            company.setRegistrationAddress(getStringValue(data, "注册地址", "所在地区"));
            company.setContactPerson(getStringValue(data, "联系人"));
            company.setContactInfo(getStringValue(data, "联系方式", "联系电话"));
            
            // 业务和需求
            company.setBusinessKeywords(getStringValue(data, "主营业务关键词", "业务关键词"));
            company.setCooperationType(getStringValue(data, "合作需求类型"));
            company.setRequiredTechField(getStringValue(data, "需求技术领域"));
            company.setRequiredExpertTitle(getStringValue(data, "需求专家职称"));
            
            // 合作状态
            company.setMatchedExpertCodes(getStringValue(data, "对接专家编号"));
            company.setCooperationStatus(getStringValue(data, "合作状态"));
            company.setCooperationHistory(getStringValue(data, "合作历史项目"));
            company.setRemarks(getStringValue(data, "备注"));
            
            LOG.info("准备保存企业: " + company.getCompanyCode() + " - " + company.getCompanyName());
            
            // 检查是否存在，存在则更新，不存在则新增
            CompanyEntity existingCompany = companyService.getByCompanyCode(company.getCompanyCode());
            if (existingCompany != null) {
                company.setId(existingCompany.getId());
                companyService.updateById(company);
                LOG.info("更新企业: " + company.getCompanyCode());
            } else {
                companyService.save(company);
                LOG.info("新增企业: " + company.getCompanyCode());
            }
            
            count++;
        }
        
        return count;
    }

    /**
     * 添加专家信息
     */
    @ApiOperation(value = "添加专家信息")
    @PostMapping("/addExpert")
    @ResponseBody
    @Transactional
    public ResultData addExpert(@RequestBody ExpertEntity expert) {
        try {
            // 检查专家编号是否已存在
            ExpertEntity existingExpert = expertService.getByExpertCode(expert.getExpertCode());
            if (existingExpert != null) {
                return ResultData.build().error("专家编号已存在，请使用其他编号");
            }
            
            // 设置序号（可选，如果需要自动生成序号）
            Integer maxSerialNo = expertService.getMaxSerialNo();
            expert.setSerialNo(maxSerialNo == null ? 1 : maxSerialNo + 1);
            
            // 保存专家信息
            boolean result = expertService.save(expert);
            if (result) {
                return ResultData.build().success("专家信息添加成功");
            } else {
                return ResultData.build().error("专家信息添加失败");
            }
        } catch (Exception e) {
            LOG.error("添加专家信息失败", e);
            return ResultData.build().error("添加专家信息失败：" + e.getMessage());
        }
    }

    /**
     * 添加项目信息
     */
    @ApiOperation(value = "添加项目信息")
    @PostMapping("/addProject")
    @ResponseBody
    @Transactional
    public ResultData addProject(@RequestBody ProjectEntity project) {
        try {
            // 检查项目编号是否已存在
            ProjectEntity existingProject = projectService.getByProjectCode(project.getProjectCode());
            if (existingProject != null) {
                return ResultData.build().error("项目编号已存在，请使用其他编号");
            }
            
            // 检查负责人是否存在
            if (StringUtils.isNotEmpty(project.getLeaderCode())) {
                ExpertEntity leader = expertService.getByExpertCode(project.getLeaderCode());
                if (leader == null) {
                    return ResultData.build().error("负责人专家不存在，请检查负责人编号");
                }
            }
            
            // 设置序号（可选，如果需要自动生成序号）
            Integer maxSerialNo = projectService.getMaxSerialNo();
            project.setSerialNo(maxSerialNo == null ? 1 : maxSerialNo + 1);
            
            // 保存项目信息
            boolean result = projectService.save(project);
            if (result) {
                return ResultData.build().success("项目信息添加成功");
            } else {
                return ResultData.build().error("项目信息添加失败");
            }
        } catch (Exception e) {
            LOG.error("添加项目信息失败", e);
            return ResultData.build().error("添加项目信息失败：" + e.getMessage());
        }
    }

    /**
     * 添加企业信息
     */
    @ApiOperation(value = "添加企业信息")
    @PostMapping("/addCompany")
    @ResponseBody
    @Transactional
    public ResultData addCompany(@RequestBody CompanyEntity company) {
        try {
            // 检查企业编号是否已存在
            CompanyEntity existingCompany = companyService.getByCompanyCode(company.getCompanyCode());
            if (existingCompany != null) {
                return ResultData.build().error("企业编号已存在，请使用其他编号");
            }
            
            // 检查对接专家是否存在
            if (StringUtils.isNotEmpty(company.getMatchedExpertCodes())) {
                String[] expertCodes = company.getMatchedExpertCodes().split(",");
                for (String expertCode : expertCodes) {
                    expertCode = expertCode.trim();
                    if (StringUtils.isNotEmpty(expertCode)) {
                        ExpertEntity expert = expertService.getByExpertCode(expertCode);
                        if (expert == null) {
                            return ResultData.build().error("对接专家不存在：" + expertCode);
                        }
                    }
                }
            }
            
            // 设置序号（可选，如果需要自动生成序号）
            Integer maxSerialNo = companyService.getMaxSerialNo();
            company.setSerialNo(maxSerialNo == null ? 1 : maxSerialNo + 1);
            
            // 保存企业信息
            boolean result = companyService.save(company);
            if (result) {
                return ResultData.build().success("企业信息添加成功");
            } else {
                return ResultData.build().error("企业信息添加失败");
            }
        } catch (Exception e) {
            LOG.error("添加企业信息失败", e);
            return ResultData.build().error("添加企业信息失败：" + e.getMessage());
        }
    }

    /**
     * 根据专家编号查询关联信息（专家、项目、公司）
     */
    @GetMapping("/getExpertRelations")
    @ResponseBody
    public ResultData getExpertRelations(@RequestParam("expertCode") String expertCode) {
        if (StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家编号不能为空");
        }
        
        // 返回结果集
        Map<String, Object> resultMap = new HashMap<>();
        
        // 1. 查询专家信息
        ExpertEntity expert = expertService.getByExpertCode(expertCode);
        if (expert == null) {
            return ResultData.build().error("未找到对应的专家信息");
        }
        resultMap.put("expert", expert);
        
        // 2. 查询关联的项目信息
        List<ProjectEntity> projects = projectService.getByLeaderCode(expertCode);
        resultMap.put("projects", projects);
        
        // 3. 查询作为参与者的项目
        List<ProjectEntity> participantProjects = projectService.getByExpertCode(expertCode);
        resultMap.put("participantProjects", participantProjects);
        
        // 4. 查询关联的企业信息
        List<CompanyEntity> companies = companyService.getByExpertCode(expertCode);
        resultMap.put("companies", companies);
        
        return ResultData.build().success(resultMap);
    }
    
    /**
     * 删除专家及其关联信息
     * 这个方法会删除专家并清理项目和企业中的关联
     */
    @ApiOperation(value = "删除专家及其关联信息")
    @PostMapping("/deleteExpert")
    @ResponseBody
    @Transactional
    public ResultData deleteExpert(@RequestBody Map<String, String> params) {
        String expertCode = params.get("expertCode");
        if (StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家编号不能为空");
        }
        
        try {
            // 1. 获取专家信息确认存在
            ExpertEntity expert = expertService.getByExpertCode(expertCode);
            if (expert == null) {
                return ResultData.build().error("未找到对应的专家信息");
            }
            
            // 2. 获取关联的项目
            List<ProjectEntity> leaderProjects = projectService.getByLeaderCode(expertCode);
            List<ProjectEntity> participantProjects = projectService.getByExpertCode(expertCode);
            
            // 3. 清理作为项目负责人的关联
            if (!leaderProjects.isEmpty()) {
                for (ProjectEntity project : leaderProjects) {
                    // 将负责人设置为空
                    project.setLeaderCode(null);
                    projectService.updateById(project);
                }
            }
            
            // 4. 清理作为项目参与者的关联
            if (!participantProjects.isEmpty()) {
                for (ProjectEntity project : participantProjects) {
                    if (project.getParticipantExperts() != null) {
                        // 从参与者字段中移除当前专家编号
                        String participants = project.getParticipantExperts();
                        participants = participants.replace(expertCode + ",", "")
                                .replace("," + expertCode, "")
                                .replace(expertCode, "");
                        project.setParticipantExperts(participants);
                        projectService.updateById(project);
                    }
                }
            }
            
            // 5. 获取关联的企业
            List<CompanyEntity> companies = companyService.getByExpertCode(expertCode);
            
            // 6. 清理企业中的专家关联
            if (!companies.isEmpty()) {
                for (CompanyEntity company : companies) {
                    // 从matched_expert_codes字段中移除当前专家编号
                    if (company.getMatchedExpertCodes() != null) {
                        String matchedExperts = company.getMatchedExpertCodes();
                        matchedExperts = matchedExperts.replace(expertCode + ",", "")
                                .replace("," + expertCode, "")
                                .replace(expertCode, "");
                        company.setMatchedExpertCodes(matchedExperts);
                    }
                    
                    companyService.updateById(company);
                }
            }
            
            // 7. 最后删除专家
            boolean success = expertService.removeById(expertCode);
            
            if (success) {
                return ResultData.build().success("专家及其关联信息已成功删除");
            } else {
                return ResultData.build().error("删除专家失败");
            }
        } catch (Exception e) {
            LOG.error("删除专家出错: " + e.getMessage(), e);
            return ResultData.build().error("删除专家时发生错误: " + e.getMessage());
        }
    }
    
    /**
     * 删除专家及其所有关联信息（完全删除模式）
     * 此方法会删除：
     * 1. 专家本身的记录
     * 2. 与该专家关联的所有项目记录
     * 3. 与该专家关联的所有企业记录
     */
    @ApiOperation(value = "完全删除专家及其所有关联信息")
    @PostMapping("/deleteWithRelations")
    @ResponseBody
    @Transactional
    public ResultData deleteWithRelations(@RequestBody Map<String, String> params) {
        String expertCode = params.get("expertCode");
        if (StringUtils.isEmpty(expertCode)) {
            return ResultData.build().error("专家编号不能为空");
        }
        
        try {
            // 1. 获取专家信息确认存在
            ExpertEntity expert = expertService.getByExpertCode(expertCode);
            if (expert == null) {
                return ResultData.build().error("未找到对应的专家信息");
            }
            
            // 2. 获取关联的项目
            List<ProjectEntity> leaderProjects = projectService.getByLeaderCode(expertCode);
            List<ProjectEntity> participantProjects = projectService.getByExpertCode(expertCode);
            
            // 记录所有相关的项目ID，避免重复
            Set<String> projectIds = new HashSet<>();
            
            // 3. 完全删除作为负责人的项目
            for (ProjectEntity project : leaderProjects) {
                projectIds.add(project.getId());
            }
            
            // 4. 完全删除作为参与者的项目
            for (ProjectEntity project : participantProjects) {
                projectIds.add(project.getId());
            }
            
            // 执行项目删除操作
            if (!projectIds.isEmpty()) {
                projectService.removeByIds(projectIds);
                LOG.info("已删除与专家{}关联的{}个项目", expertCode, projectIds.size());
            }
            
            // 5. 获取关联的企业
            List<CompanyEntity> companies = companyService.getByExpertCode(expertCode);
            
            // 记录所有相关的企业ID
            Set<String> companyIds = new HashSet<>();
            
            // 6. 完全删除关联的企业
            for (CompanyEntity company : companies) {
                companyIds.add(company.getId());
            }
            
            // 执行企业删除操作
            if (!companyIds.isEmpty()) {
                companyService.removeByIds(companyIds);
                LOG.info("已删除与专家{}关联的{}个企业", expertCode, companyIds.size());
            }
            
            // 7. 最后删除专家
            boolean success = expertService.removeById(expertCode);
            
            if (success) {
                return ResultData.build().success("已完全删除专家及其关联的" + projectIds.size() + "个项目和" + companyIds.size() + "个企业数据");
            } else {
                return ResultData.build().error("删除专家失败");
            }
        } catch (Exception e) {
            LOG.error("完全删除专家及关联数据出错: " + e.getMessage(), e);
            return ResultData.build().error("删除过程中发生错误: " + e.getMessage());
        }
    }

    /**
     * 数据管理页面
     */
    @ApiOperation(value = "数据管理页面")
    @GetMapping("/management")
    public String management() {
        LOG.info("Accessing data management page");
        return "/expertmanage/relation/management";
    }
} 